# -*- coding: utf-8 -*-
"""
@Time    : 2021/6/18 16:21
@Author  : jins
@File    : OperateMysql.py
"""

import pymysql


class OperateMysql:
    """
    操作mysql数据库，包含初始化，增删改查，提交，关闭，回滚等操作
    """

    def __init__(self, host='10.0.17.17', user='xluser', password='xluser', database='', port=3306):
        self.conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        self.cur = self.conn.cursor()

    def commit(self):
        # 提交事务
        try:
            self.conn.commit()
        except Exception:
            print('提交失败！')

    def close(self):
        # 关闭游标，关闭链接
        try:
            self.cur.close()
            self.conn.close()
        except Exception:
            print('关闭数据库失败')

    def rollback(self):
        # 回滚
        try:
            self.conn.rollback()
        except Exception:
            print('回滚失败')

    @staticmethod
    def dict_2_str_and(dictin):
        """
        将字典变成，key='value' and key='value'的形式
        """
        tmplist = []
        for k, v in dictin.items():
            tmp = "%s='%s'" % (str(k), (str(v)))
            tmplist.append(' ' + tmp + ' ')
        return ' and '.join(tmplist)

    @staticmethod
    def dict_2_str(dictin):
        """
        将字典变成，key='value',key='value' 的形式
        """
        tmplist = []
        for k, v in dictin.items():
            tmp = "%s='%s'" % (str(k), (str(v)))
            tmplist.append(' ' + tmp + ' ')
        return ','.join(tmplist)

    def search_data(self, table_name, conditions=None):
        """
        查询操作
        :param table_name: 表名
        :param conditions: 筛选条件，字典形式传入
        :return: 查询到的数据（元组）
        """
        tmp = self.dict_2_str_and(conditions)
        if conditions:
            sql = f"""select * from {table_name} where """ + tmp  # 按条件查
        else:
            sql = f"""select * from {table_name}"""  # 查整张表
        try:
            self.cur.execute(sql)
        except Exception as e:
            print(e)
        finally:
            res = self.cur.fetchall()
            # self.close()
        return res

    def insert_data(self, table_name, values):
        """
        插入操作
        :param table_name: 表名
        :param values: 插入的内容（字符串）
        :return: None
        """
        sql = f"insert into {table_name} values " + "(" + values + ")"
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            self.rollback()
            print(e)
        finally:
            # self.close()
            pass

    def update_data(self, table_name, values, conditions):
        """
        更新操作
        :param table_name: 表名
        :param values: 需要更新的字段（字典）
        :param conditions: 查询筛选条件（字典）
        :return: None
        """
        tmp = self.dict_2_str(values)
        tmp2 = self.dict_2_str_and(conditions)
        sql = f"""update {table_name} set {tmp} where {tmp2}"""
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            pass

    def delete_data(self, table_name, condition=None):
        """
        删除操作
        :param table_name: 表名
        :param condition: 删除的where条件，如果为空，则删除该表所有数据
        :return: None
        """
        tmp = self.dict_2_str_and(condition)
        if condition:
            sql = f"""delete from {table_name} where {tmp}"""
        else:
            sql = f"""delete * from {table_name}"""
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            # self.close()
            pass

    def excute_sql(self, sql):
        """
        执行一条sql语句
        :param sql: 需要执行的sql语句
        :return: 如果是查询语句，返回结果
        """
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            self.close()
            res = self.cur.fetchall()
        return res


if __name__ == '__main__':
    op = OperateMysql(database='unit_center')
    # op.insert_data('event_push_job', """NULL,
    # 	5,
    # 	1,
    # 	'event_type',
    # 	'1',
    # 	'wechat',
    # 	'test01',
    # 	'{"templateId":"BuEaHfp8AmgzAy_G3EI5HN0MhEcDZTxmUUIFG1iB45E","templatedata":{"pushMessage":"test01","test":123}}',
    # 	'{"test":123}',
    # 	'{"openIds":["oTsaW5ljzY1VaNxX54HuFrAnhSiU"],"appId":"wx5913c8b5cd59fc8d","secret":"470ddf734403b181738ada5fd4285e75"}',
    # 	'2021-06-18 14:30:00',
    # 	1,
    # 	0,
    # 	1,
    # 	0,
    # 	0,
    # 	'test',
    # 	'jins_test',
    # 	'2021-06-18 09:45:00',
    #     'jins_test',
    #     '2021-06-18 09:45:00',
    #     0""")
    sql = """select count(1) from unit"""
    res = op.excute_sql(sql)
    print(res[0][0])
